/**
 * 
 */
package com.ucom;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.LinkedList;

/**
 * @author tathagata
 * 
 */
public class UcomPrinterUsageDAO {

	/**
	 * 
	 */
	private Connection dbConnection;
	private String query;
	private ResultSet rslt;
	private Statement stmtGetListEntries;

	public UcomPrinterUsageDAO() {
		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver");
			dbConnection = DriverManager.getConnection(
					"jdbc:derby://localhost:1527/UCOM");
			stmtGetListEntries = dbConnection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

		} catch (ClassNotFoundException cnfe) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(cnfe.getMessage());

		} catch (SQLException sqle) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(sqle.getMessage());
		}

	}

	public LinkedList<Query1> query1() {
		LinkedList<Query1> result = new LinkedList<Query1>();
		try {
			query = "SELECT OFFICE_ID AS \"OFFICE_ID\", COMMAND AS \"COMMAND\", COUNT(COMMAND) AS \"USAGE\"  "
				+ "FROM USAGE_HISTORY, USER_INFO "
				+ "WHERE USAGE_HISTORY.USER_ID = USER_INFO.USER_ID "
				+ "AND USER_INFO.OFFICE_ID IN (SELECT OFFICE_ID FROM OFFICE_INFO) "
				+ "AND USAGE_HISTORY.COMMAND IN ('lpr','lp','lprm','lpq') "
				+ "GROUP BY OFFICE_ID, COMMAND";

			rslt = stmtGetListEntries.executeQuery(query);

			while (rslt.next()) {
				System.out.print(rslt.getInt(1));
				System.out.print("\t" + rslt.getString(2));
				System.out.println("\t" + rslt.getInt(3));
				result.add(new Query1(rslt.getInt(1), rslt.getString(2), rslt.getInt(3)));
			}
			rslt.close();
		} catch (SQLException sqle) {
			System.out.println("Error in getting transaction details");
			System.out.println(sqle.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

}
